/**
 * 房间管理相关的SQL语句集合。
 * @module sqlStatements
 */

const sqlStatements = {
  /**
   * 创建历史记录
   * @param {number} roomNumber - 房间编号，将替换第一个“?”占位符。
   */
  createUserHistorySql: `
  CREATE TABLE IF NOT EXISTS user_?_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    roomId INT NOT NULL,
    MID INT NOT NULL,
    title VARCHAR(255),
    type ENUM('0', '1') NOT NULL,
    startTime TIMESTAMP NOT NULL,
    nearTime TIMESTAMP,
    endTime TIMESTAMP NOT NULL,
    continuousTime VARCHAR(255),
    host VARCHAR(255) NOT NULL
  );
  `,

  /**
   * 向历史记录表中插入数据
   */
  insertUserHistorySql: `
    INSERT INTO user_?_history (roomId, MID, title, type, startTime,nearTime,endTime,continuousTime, host) VALUES (?,?,?,?,?,?,?,?,?);
    `,

  /**
   * 修改对应的info表的结束时间的触发器
   */
  updateUserInfoEndTimeSql: `
    CREATE TRIGGER update_?_info_endTime
    AFTER INSERT ON user_?_history
    FOR EACH ROW
    BEGIN
      UPDATE room_?_info
      SET endTime = NEW.endTime
      WHERE MID = NEW.MID;
    END;
    `,

  // 查询最后最后一条历史记录
  selectUserLastRecoreSql: `
    SELECT * FROM user_?_history ORDER BY id DESC LIMIT 1;
    `,

  // 更新历史记录
  updateUserHistorySql: `
    UPDATE user_?_history
    SET nearTime = ?, endTime = ?, continuousTime = ?
    WHERE roomId = ? AND MID = ?;
  `,

  // 查询创建或者加入历史记录
  selectCreatedHistorySql: `
    SELECT * FROM user_?_history WHERE type = ?
  `,

  // 删除一条历史记录
  deleteUserHistoryByIdSql: `
    DELETE FROM user_?_history WHERE id = ?
  `,

  // 删除多条历史记录
  deleteUserHistoryByIdsSql: id => {
    const placeholders = id.map(() => '?').join(',')
    return `
    DELETE FROM user_?_history WHERE id In (${placeholders})
    `
  }
}

module.exports = sqlStatements
